The embodiments disclosed and claimed herein are related to computer systems, and more particularly, databases.
Today""s businesses have sophisticated data analysis requirements. The metrics or analyses of a business""s data can be difficult to obtain. To calculate a meaningful metric, business analysts often use spreadsheets to manually analyze data. Manual analysis, of course, is a tedious and time-consuming process.
Most applications fail to deliver useful metrics that provide unique insights into an organization""s performance. Useful metrics highlight significant performance measures of the business. Typically, business analysts must execute multiple queries and other time-consuming manual interventions to produce these metrics. Then, despite the time-consuming effort, analysts must start the process anew to obtain follow-up information such as an explanation of a particular anomaly in a metric.
Typically, a business""s data is stored on a database or on databases. These databases are operated with associated database servers, which manage the storage and retrieval of records from the databases. Analytical servers have additionally been provided to format database queries or information requests sent from a client user interface to the database server for handling. The analytical servers can be used to improve the efficiency of the database accesses and to provide metrics of interest to the user from the retrieved records from the database.
The embodiments disclosed below provide an analytical server which efficiently accesses a Relational Database Management System (xe2x80x9cRDBMSxe2x80x9d) comprising a database and a database server. The database in this approach includes fact and dimension tables which may be, for example, configured in a star schema having a central base_fact table with surrounding dimension tables to form the star structure. Aggregate_fact tables may also be provided which aggregate measures from the base_fact table at a higher hierarchical level than such measures are maintained in the base_fact table. Metadata is further stored in the database, where the metadata describes the organization of the various tables in the database, and specifically the metadata in the embodiments described below includes information about the hierarchical levels of various dimensions of the above-mentioned tables and star schema.
With further reference to the metadata stored in the database in the below-described embodiments, the analytical server described herein receives the metadata from the database and analyzes that metadata, including the hierarchical information, in order to provide relatively efficient access to the tables of the database in response to a query from a user. Such efficient access preferably supports calculation of complex metrics which might otherwise be difficult or impossible. Supported levels of stars are defined and analyzed in a sophisticated and efficient manner which facilitates the calculation of chameleon and allocated metrics.
The foregoing provides a number of additional advantages. A user can easily limit the data to a particular set of value(s) for a particular hierarchy level, known as slicing The user can also view the metrics by moving up or down through a hierarchy, known as drilling. Additionally, fact level security and dimensional security are supported, as well as efficient collection and analysis of aggregate_fact table usage statistics.